package com.example.electricbicycle.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import utils.DBUtils;

public class ReportDAOImpl implements ReportDAO {

    @Override
    public List<Map<String, Object>> getPurchaseStatsByUser(Date startDate, Date endDate) {
        String sql = "SELECT u.id, u.name, COUNT(po.id) AS order_count, SUM(po.quantity) AS total_quantity " +
                "FROM purchase_order po " +
                "JOIN user u ON po.user_id = u.id " +
                "WHERE po.create_time BETWEEN ? AND ? " +
                "GROUP BY u.id, u.name";
        return executeStatsQuery(sql, startDate, endDate);
    }

    @Override
    public List<Map<String, Object>> getPurchaseStatsByTime(Date startDate, Date endDate) {
        String sql = "SELECT DATE(create_time) AS order_date, COUNT(id) AS order_count, SUM(quantity) AS total_quantity " +
                "FROM purchase_order " +
                "WHERE create_time BETWEEN ? AND ? " +
                "GROUP BY DATE(create_time) " +
                "ORDER BY order_date";
        return executeStatsQuery(sql, startDate, endDate);
    }

    @Override
    public List<Map<String, Object>> getSaleStatsByUser(Date startDate, Date endDate) {
        String sql = "SELECT u.id, u.name, COUNT(so.id) AS order_count, SUM(so.quantity) AS total_quantity, SUM(vi.price * so.quantity) AS total_amount " +
                "FROM sale_order so " +
                "JOIN user u ON so.user_id = u.id " +
                "JOIN vehicle_info vi ON so.v_id = vi.id " +
                "WHERE so.sale_time BETWEEN ? AND ? " +
                "GROUP BY u.id, u.name";
        return executeStatsQuery(sql, startDate, endDate);
    }

    @Override
    public List<Map<String, Object>> getSaleStatsByTime(Date startDate, Date endDate) {
        String sql = "SELECT DATE(sale_time) AS order_date, COUNT(id) AS order_count, SUM(quantity) AS total_quantity, SUM(vi.price * so.quantity) AS total_amount " +
                "FROM sale_order so " +
                "JOIN vehicle_info vi ON so.v_id = vi.id " +
                "WHERE so.sale_time BETWEEN ? AND ? " +
                "GROUP BY DATE(sale_time) " +
                "ORDER BY order_date";
        return executeStatsQuery(sql, startDate, endDate);
    }

    @Override
    public List<Map<String, Object>> getAfterSaleStatsByUser(Date startDate, Date endDate) {
        String sql = "SELECT u.id, u.name, COUNT(aso.id) AS order_count, " +
                "SUM(CASE WHEN aso.status = 2 THEN 1 ELSE 0 END) AS completed_count " +
                "FROM after_sale_order aso " +
                "JOIN user u ON aso.user_id = u.id " +
                "WHERE aso.create_time BETWEEN ? AND ? " +
                "GROUP BY u.id, u.name";
        return executeStatsQuery(sql, startDate, endDate);
    }

    @Override
    public List<Map<String, Object>> getAfterSaleStatsByTime(Date startDate, Date endDate) {
        String sql = "SELECT DATE(create_time) AS order_date, COUNT(id) AS order_count, " +
                "SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS completed_count " +
                "FROM after_sale_order " +
                "WHERE create_time BETWEEN ? AND ? " +
                "GROUP BY DATE(create_time) " +
                "ORDER BY order_date";
        return executeStatsQuery(sql, startDate, endDate);
    }

    private List<Map<String, Object>> executeStatsQuery(String sql, Date startDate, Date endDate) {
        List<Map<String, Object>> resultList = new ArrayList<>();

        try (Connection conn = DBUtils.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setTimestamp(1, new java.sql.Timestamp(startDate.getTime()));
            pstmt.setTimestamp(2, new java.sql.Timestamp(endDate.getTime()));

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        String columnName = rs.getMetaData().getColumnLabel(i);
                        row.put(columnName, rs.getObject(i));
                    }
                    resultList.add(row);
                }
            }
        } catch (SQLException e) {
            System.err.println("报表统计查询失败: " + e.getMessage());
            e.printStackTrace();
        }

        return resultList;
    }
}